﻿using FinancialReportPowerPointAddIn.Helper;
using Microsoft.Office.Interop.PowerPoint;
using System;
using System.Drawing;
using System.Linq;
using PpChartType = Microsoft.Office.Core.XlChartType;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;

namespace FinancialReportPowerPointAddIn.PPTModule
{
    public static class GeneratePlatformMdsSalesTrend
    {
        public static void Write(int year, int month)
        {
            //获取当前ppt中所有的幻灯片
            var slides = Globals.ThisAddIn.Application.ActivePresentation.Slides;
            //在当前选中幻灯片前插入新的幻灯片--代码提示中没有F12进去才找到的方法
            Slide currentslide = slides.Add(slides.Count + 1, PpSlideLayout.ppLayoutBlank);
            currentslide.Select();
            //开始写入数据            
            string[] restaurants = new string[] { "潭江店", "开大店", "白石店", "西粤店", "东湖店", "恩平店", "油十店", "汇总" };
            using (var db = new FinancialReportContext())
            {
                //写入平台差异图表                
                Chart chart = currentslide.Shapes.AddChart2(
                Style: 227,
                    Type: PpChartType.xlLine,
                    Left: 0,
                    Top: 30,
                    Width: currentslide.Master.Width,
                    Height: currentslide.Master.Height-30,
                    NewLayout: true).Chart;
                // 设置图表的数据
                // 获取图表的数据源工作表  
                var worksheet = chart.ChartData.Workbook.Worksheets[1];
                // 清除之前的数据
                worksheet.UsedRange.Clear();
                // 修改工作表中的数据
                worksheet.Cells[1, 1].Value = $"MDSSales13个月趋势{year-1}{month.ToString("00")}-{year}{month.ToString("00")}";
                int row = 2;
                foreach (string item in restaurants)
                {
                    worksheet.Cells[row, 1].Value = item;
                    row++;
                }
                int col = 2;
                for (int i = -12; i <= 0; i++)
                {
                    row = 1;
                    DateTime date = new DateTime(year: year, month: month, day: 1).AddMonths(i);
                    worksheet.Cells[row, col].Value = $"{date.Month}月";
                    row++;
                    foreach (var item in restaurants)
                    {
                        Common.GetPLNameAndCode(item, out string flag, out string code);
                        if (item == "汇总")
                        {
                            var cplatformsales = db.PlatformSales.Where(x => x.Year == date.Year && x.Month == date.Month).Sum(x => x.MDS营业净收入);
                            var lyplatformsales = db.PlatformSales.Where(x => x.Year == date.Year - 1 && x.Month == date.Month).Sum(x => x.MDS营业净收入);
                            worksheet.Cells[row, col].Value = Calculate.CalVar(cplatformsales, lyplatformsales).ToString("p");
                        }
                        else
                        {
                            var cplatformdata = db.PlatformSales.FirstOrDefault(x => x.Uscode == code && x.Year == date.Year && x.Month == date.Month);
                            var lyplatformdata = db.PlatformSales.FirstOrDefault(x => x.Year == date.Year - 1 && x.Month == date.Month && x.Uscode == code);
                            worksheet.Cells[row, col].Value = Calculate.CalVar(cplatformdata.MDS营业净收入, lyplatformdata.MDS营业净收入).ToString("p");
                        }
                        row++;
                    }
                    col++;
                }
                // 设置图表的数据范围            
                string rangeAddress = "='Sheet1'!" + worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[9, 14]].Address;
                chart.SetSourceData(rangeAddress, XlRowCol.xlRows);
                // 保存并关闭Excel工作簿
                chart.ChartData.Workbook.Close(true);
                // 添加图表标题
                chart.HasTitle = true;
                chart.ChartTitle.Text = $"MDSSales13个月趋势{year - 1}{month.ToString("00")}-{year}{month.ToString("00")}";
                // 添加图例
                chart.HasLegend = true;
                //设置系列1的填充颜色为红色
                chart.SeriesCollection(1).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(0, 178, 238));
                chart.SeriesCollection(2).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(255, 140, 105));
                chart.SeriesCollection(3).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(54, 54, 54));
                chart.SeriesCollection(4).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(255, 165, 0));
                chart.SeriesCollection(5).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(165, 42, 42));
                chart.SeriesCollection(6).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(144, 238, 144));
                chart.SeriesCollection(7).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(209, 95, 238));
                chart.SeriesCollection(8).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(130, 130, 130));
                //设置线段类型
                chart.SeriesCollection(1).Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSysDash;
                chart.SeriesCollection(2).Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSysDash;
                chart.SeriesCollection(3).Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSysDash;
                chart.SeriesCollection(4).Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSysDash;
                chart.SeriesCollection(8).Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSysDot;
                // 取消横向网格线
                chart.Axes(XlAxisType.xlValue).HasMajorGridlines = false;
                chart.Axes(XlAxisType.xlValue).HasMinorGridlines = false;
                // 取消纵向网格线
                chart.Axes(XlAxisType.xlCategory).HasMajorGridlines = false;
                chart.Axes(XlAxisType.xlCategory).HasMinorGridlines = false;
                //数据绘图区填充颜色
                chart.PlotArea.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(233, 233, 238).ToArgb();
                // 取消显示横向坐标轴
                //chart.Axes(XlAxisType.xlValue).MajorGridlines.Delete();
                //chart.Axes(XlAxisType.xlValue).HasMajorGridlines = false;
                //chart.Axes(XlAxisType.xlValue).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionNone;
                // 取消显示纵向坐标轴
                //chart.Axes(XlAxisType.xlCategory).MajorGridlines.Delete();
                //chart.Axes(XlAxisType.xlCategory).HasMajorGridlines = false;
                //chart.Axes(XlAxisType.xlCategory).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionNone;
                // 设置整个图表的字体大小
                chart.ChartArea.Format.TextFrame2.TextRange.Font.Size = 10;
                // 设置图表标题字体大小
                chart.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 20;
                chart.ChartTitle.Format.TextFrame2.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
                // 显示图表的数据表
                chart.HasDataTable = true;
            }
        }
    }
}
